﻿
-- Excluir funções de inserção -- 
DROP FUNCTION IF EXISTS buscarArvoreCenso(
	raioCopaMinimo		real,
	raioCopaMaximo		real,
	areaCopaMinimo		real,
	areaCopaMaximo		real,
	alturaArvore		char,
	alturaBifArvore		char,
	plantioArvore		char,
	danoArvore		char,
	situacaoArvore		char,
	matrizArvore		char,
	nomeRuaArvore		varchar(60),
	nomeBairroArvore	varchar(40),
	nomeCientificoArvore 	varchar(50),
	nomePopular		varchar(50),
	nomeGenero		varchar(30)
);

-- buscar arvore por todos parametros --
CREATE OR REPLACE FUNCTION buscarArvoreCenso(
	raioCopaMinimo		real DEFAULT NULL,
	raioCopaMaximo		real DEFAULT NULL,
	areaCopaMinimo		real DEFAULT NULL,
	areaCopaMaximo		real DEFAULT NULL,
	alturaArvore		char DEFAULT NULL,
	alturaBifArvore		char DEFAULT NULL,
	plantioArvore		char DEFAULT NULL,
	danoArvore		char DEFAULT NULL,
	situacaoArvore		char DEFAULT NULL,
	matrizArvore		char DEFAULT NULL,
	nomeRuaArvore		varchar(60) DEFAULT NULL,
	nomeBairroArvore	varchar(40) DEFAULT NULL,
	nomeCientificoArvore 	varchar(50) DEFAULT NULL,
	nomePopularArvore 	varchar(50) DEFAULT NULL,
	nomeGeneroArvore 	varchar(30) DEFAULT NULL,
	origemArvore		char DEFAULT NULL
	) RETURNS SETOF record
AS $$
DECLARE
	raioCopaMinimo		ALIAS for $1;
	raioCopaMaximo		ALIAS for $2;
	areaCopaMinimo		ALIAS for $3;
	areaCopaMaximo		ALIAS for $4;
	alturaArvore		ALIAS for $5;
	alturaBifArvore		ALIAS for $6;
	plantioArvore		ALIAS for $7;
	danoArvore		ALIAS for $8;
	situacaoArvore		ALIAS for $9;
	matrizArvore		ALIAS for $10;
	nomeRuaArvore		ALIAS for $11;
	nomeBairroArvore	ALIAS for $12;
	nomeCientificoArvore	ALIAS for $13;
	nomePopularArvore	ALIAS for $14;
	nomeGeneroArvore	ALIAS for $15;
	origemArvore		ALIAS for $16;
BEGIN
	RETURN QUERY SELECT
		dataColeta,
		numeroOrdem,
		nome_rua,
		nome_bairro,
		nome_coletor,
		nome_cientifico,
		nome_popular,
		nome_genero,
		origem,
		raioCopa,
		areaCopa,
		numeroProximo,
		posicao,
		altura,
		alturaBif,
		plantio,
		dano,
		situacao,
		matriz
	FROM arvore,rua,coletor,tr_rua_coletor,bairro,especie,genero WHERE
		arvore.id_rua = rua.ruaId AND
		arvore.id_especie = especie.especieId AND
		rua.id_bairro = bairro.bairroId AND
		arvore.id_rua = tr_rua_coletor.id_rua AND
		tr_rua_coletor.id_coletor = coletor.coletorId AND
		especie.id_genero = genero.generoId AND
		(raioCopaMinimo IS NULL OR arvore.raioCopa >= raioCopaMinimo) AND
		(raioCopaMaximo IS NULL OR arvore.raioCopa <= raioCopaMaximo) AND
		(areaCopaMinimo IS NULL OR arvore.areaCopa >= areaCopaMinimo) AND
		(areaCopaMaximo IS NULL OR arvore.areaCopa <= areaCopaMaximo) AND
		(alturaArvore IS NULL OR arvore.altura = alturaArvore) AND
		(alturaBifArvore IS NULL OR arvore.alturaBif = alturaBifArvore) AND
		(plantioArvore IS NULL OR arvore.plantio = plantioArvore) AND
		(danoArvore IS NULL OR arvore.dano = danoArvore) AND
		(situacaoArvore IS NULL OR arvore.situacao = situacaoArvore) AND
		(matrizArvore IS NULL OR arvore.matriz = matrizArvore) AND
		(nomeRuaArvore IS NULL OR rua.nome_rua = nomeRuaArvore) AND
		(nomeBairroArvore IS NULL OR bairro.nome_bairro = nomeBairroArvore) AND
		(nomeCientificoArvore IS NULL OR especie.nome_cientifico = nomeCientificoArvore) AND
		(nomePopularArvore IS NULL OR especie.nome_popular = nomePopularArvore) AND
		(nomeGeneroArvore IS NULL OR genero.nome_genero = nomeGeneroArvore) AND
		(origemArvore IS NULL OR especie.origem = origemArvore) 
		ORDER BY nome_bairro, nome_rua, numeroOrdem;
RETURN;
END; $$ LANGUAGE 'plpgsql' VOLATILE;

-- exemplo de utilização --
SELECT * FROM buscarArvoreCenso(
	NULL,			-- raioCopaMinimo	real DEFAULT NULL,
	NULL,			-- raioCopaMaximo	real DEFAULT NULL,
	NULL,			-- areaCopaMinimo	real DEFAULT NULL,
	NULL,			-- areaCopaMaximo	real DEFAULT NULL,
	NULL,			-- alturaArvore		char DEFAULT NULL,
	NULL,			-- alturaBifArvore	char DEFAULT NULL,
	NULL,			-- plantioArvore	char DEFAULT NULL,
	NULL,			-- danoArvore		char DEFAULT NULL,
	NULL,			-- situacaoArvore	char DEFAULT NULL,
	NULL,			-- matrizArvore		char DEFAULT NULL,
	NULL,			-- nomeRuaArvore	varchar(60) DEFAULT NULL,
	NULL,			-- nomeBairroArvore	varchar(40) DEFAULT NULL,
	NULL,			-- nomeCientificoArvore varchar(50) DEFAULT NULL
	NULL,			-- nomePopularArvore 	varchar(50) DEFAULT NULL
	NULL,			-- nomeGeneroArvore 	varchar(30) DEFAULT NULL
	NULL			-- origemArvore 	char DEFAULT NULL
) AS (
	"Data da Coleta"		date,
	"Ordem de Coleta"		integer,
	"Logradouro"			varchar(60),
	"Bairro"			varchar(40),
	"Coletor"			varchar(40),
	"Espécie" 			varchar(50),
	"Nome Popular"			varchar(50),
	"Gênero"			varchar(30),
	"Origem"			char,
	"Raio de Copa (m)"		real,
	"Área de Copa (m²)"		real,
	"Número Próximo"		integer,
	"Posição"			char,
	"Altura (m)"			char,
	"Altura da Bifurcação (m)"	char,
	"Tipo de Plantio"		char,
	"Dano"				char,
	"Situação"			char,
	"Matriz"			char
);
	

-- retorna numero de arvores cadastradas --
CREATE or REPLACE FUNCTION numeroArvore() RETURNS INTEGER AS 
$$
DECLARE
	numeroArvore	INTEGER;
BEGIN
	numeroArvore := (SELECT MAX(arvore.arvoreId) FROM arvore);

	RETURN numeroArvore;
END
$$
LANGUAGE plpgsql;

-- retorna numero de ruas cadastradas --
CREATE or REPLACE FUNCTION numeroRua() RETURNS INTEGER AS 
$$
DECLARE
	numeroRua	INTEGER;
BEGIN
	numeroRua := (SELECT MAX(rua.ruaId) FROM rua);

	RETURN numeroRua;
END
$$
LANGUAGE plpgsql;

-- retorna numero de bairros cadastrados --
CREATE or REPLACE FUNCTION numeroBairro() RETURNS INTEGER AS 
$$
DECLARE
	numeroBairro	INTEGER;
BEGIN
	numeroBairro := (SELECT MAX(bairro.bairroId) FROM bairro);

	RETURN numeroBairro;
END
$$
LANGUAGE plpgsql;

-- retorna numero de especies cadastradas --
CREATE or REPLACE FUNCTION numeroEspecie() RETURNS INTEGER AS 
$$
DECLARE
	numeroEspecie	INTEGER;
BEGIN
	numeroEspecie := (SELECT MAX(especie.especieId) FROM especie);

	RETURN numeroEspecie;
END
$$
LANGUAGE plpgsql;

-- retorna numero de generos cadastrados --
CREATE or REPLACE FUNCTION numeroGenero() RETURNS INTEGER AS 
$$
DECLARE
	numeroGenero	INTEGER;
BEGIN
	numeroGenero := (SELECT MAX(genero.generoId) FROM genero);

	RETURN numeroGenero;
END
$$
LANGUAGE plpgsql;


-- utilizacao --
SELECT * FROM numeroArvore();
SELECT * FROM numeroRua();
SELECT * FROM numeroBairro();
SELECT * FROM numeroEspecie();
SELECT * FROM numeroGenero();

select * from arvore;
select * from rua;
select * from bairro;
select * from especie;
